/**
 * 로그정보
 *
 * @author KJH
 * @version 1.0, 2012-03-15
 * @fix([수정자]) [yyyy-mm-dd]: [수정 내용]
 *
 */

package com.ubst.smartad.impl;

import java.sql.*;
import java.util.*;
import java.io.*;

import com.ubst.smartad.dao.LogDataDO;
import com.ubst.smartad.common.TimeUtil;
import com.ubst.smartad.logger.Debug;

public class LogDataMngImpl{

	Connection con = null;
	private String serverName;
	private Debug debug;
	//private FileHostEnv fhostEnv;


    /** 
     * 생성자
     *   
     *   
    */ 
	public LogDataMngImpl(Connection con, String serverName, String log_level) {
		this.con = con;
		this.serverName = serverName;
		this.debug = new Debug("LogDataMngImpl", log_level);
		//this.fhostEnv = FileHostEnv.getInstance(serverName);

	}

	/**
	 * @author : KJH 
	 * 로그 정보를 추가한다.
	 *
	 */
	public boolean addLog( LogDataDO logdata ) {
		PreparedStatement pstmt=null;
		//System.out.println("로그등록");
		boolean insert_result = false;
		
		try {

			long l_cur_date = TimeUtil.getCurrentTime();

			StringBuffer qrybuf = new StringBuffer();
			qrybuf.append("INSERT INTO JX_DRIVE_LOG ")
				.append(" ( ")
				.append(" DOMAIN ,USER_ID ,ORG_CODE ,FILE_NAME ,FILE_SIZE ")
				.append(" ,USER_IP ,LOG_TYPE ,FDIR_NAME ,LOG_DATE ")
				.append(" )VALUES( ")
				.append(" ? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ")
				.append(" ) ");

			debug.debug("addLog ==> "+qrybuf.toString());

			pstmt = con.prepareStatement( qrybuf.toString() );

			pstmt.setString(1, logdata.domain);
			pstmt.setString(2, logdata.user_id);
			pstmt.setString(3, logdata.org_code);
			pstmt.setString(4, logdata.file_name);
			pstmt.setDouble(5, logdata.file_size);
			pstmt.setString(6, logdata.user_ip);
			pstmt.setString(7, logdata.log_type);
			pstmt.setString(8, logdata.fdir_name);
			pstmt.setString(9, TimeUtil.getDateString(l_cur_date, "yyyyMMddHHmmss"));
	
			pstmt.execute();
			pstmt.close();
			qrybuf = null;
							                                                                                                                                                                                                                         
		}catch (SQLException e){
			e.printStackTrace();
		}finally{
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return insert_result;
	}
	/**
	 * @author MYM
	 * 로그데이터의 총갯수를 가져온다
	 * **/
	public int getAllLogData() throws SQLException {
		PreparedStatement pstmt=null;
		ResultSet rs = null;

		int total_log = 0;

		try {
	
			StringBuffer qrybuf = new StringBuffer();
			qrybuf.append("SELECT COUNT(LOG_SERIAL) TOT_USER_CNT")
				.append(" FROM JX_DRIVE_LOG");
			
			debug.debug("cntLog ==> "+qrybuf.toString());	                                                                                                                                                                                                                         
			pstmt = con.prepareStatement(qrybuf.toString());
			rs = pstmt.executeQuery();

			if ( rs.next() ){
				total_log = rs.getInt("TOT_USER_CNT");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return total_log;
	}
	
	/**
	 * @author MYM
	 * 로그데이터의 리스트를 가져온다.
	 * **/
	public LogDataDO[] getLogList(int pageNum, int list_cnt) throws SQLException{
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		Vector v_log = new Vector();
		LogDataDO log_data_info = null;
		LogDataDO[] log_datas = null;
		try{
			StringBuffer qrybuf = new StringBuffer();
			qrybuf.append("SELECT")
					.append("  A.LOG_SERIAL LOG_SERIAL, A.USER_ID USER_ID, A.ORG_CODE ORG_CODE ")
					.append("  ,A.FILE_NAME FILE_NAME, A.FILE_SIZE FILE_SIZE, A.USER_IP USER_IP ")
					.append("  ,A.LOG_TYPE LOG_TYPE, A.FDIR_NAME FDIR_NAME, A.LOG_DATE LOG_DATE ")
					.append(" FROM JX_DRIVE_LOG A")
					.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");

			int startCount = (pageNum-1)*list_cnt;
			pstmt = con.prepareStatement(qrybuf.toString());
			//pstmt.setString(1, serverName);
			pstmt.setInt(1, startCount);
			pstmt.setInt(2, list_cnt);
			debug.debug("<!> getLogList Query ==> "+qrybuf.toString());

		rs=pstmt.executeQuery();
		while(rs.next()){
			log_data_info=new LogDataDO();
			log_data_info.log_serial = rs.getInt("LOG_SERIAL");
			log_data_info.domain = serverName;
			log_data_info.user_id = rs.getString("USER_ID");
			log_data_info.org_code = rs.getString("ORG_CODE");
			log_data_info.file_name = rs.getString("FILE_NAME");
			log_data_info.file_size = rs.getDouble("FILE_SIZE");
			log_data_info.user_ip = rs.getString("USER_IP");
			log_data_info.log_type = rs.getString("LOG_TYPE");
			log_data_info.fdir_name = rs.getString("FDIR_NAME");
			log_data_info.log_date = rs.getString("LOG_DATE");
			
			v_log.add(log_data_info);
			log_data_info = null;
		}
		rs.close();
		pstmt.close();


		int log_cnt = v_log.size();
		if(log_cnt>0){
			log_datas = new LogDataDO[log_cnt];
			for(int i=0;i<log_cnt;i++){
				log_datas[i] = (LogDataDO)v_log.elementAt(i);

			}
		}
	}catch (SQLException e){
		throw e;
	}finally{
		if ( rs != null) try{rs.close();}catch(SQLException ee){}
		if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
	}
	return log_datas;
}
	
/**
 * @author MYM
 * 검색별 검색데이터의 총 갯수를 가져온다
 * **/
public int getTypeDateLogData(String type_date_start,String type_date_end,String type_log_type,String type_user_id,String type_date_ym) throws SQLException {
	PreparedStatement pstmt=null;
	ResultSet rs = null;

	int total_log = 0;
	String start=type_date_start;
	String end=type_date_end;
	String logtype=type_log_type;
	String userid="%"+type_user_id+"%";
	String ym=type_date_ym+"%";



	try {

		StringBuffer qrybuf = new StringBuffer();
		qrybuf.append("SELECT COUNT(LOG_SERIAL) TOT_USER_CNT")
			.append(" FROM JX_DRIVE_LOG WHERE ");

		if(!type_log_type.equals("")&&type_user_id.equals("")&&type_date_start.equals("")&&type_date_end.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" LOG_TYPE=? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, logtype);
		}else if(!type_user_id.equals("")&&type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" USER_ID LIKE ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, userid);
		}else if(!type_date_start.equals("")&&!type_date_end.equals("")&&type_user_id.equals("")&&type_log_type.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, start);
			pstmt.setString(2, end);
		}else if(!type_user_id.equals("")&&!type_user_id.equals("")&&type_date_start.equals("")&&type_date_end.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" LOG_TYPE=? ")
			.append(" AND USER_ID LIKE ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, logtype);
			pstmt.setString(2, userid);
		}else if(!type_log_type.equals("")&&!type_date_start.equals("")&&!type_date_end.equals("")&&type_user_id.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" LOG_TYPE=? ")
			.append(" AND SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, logtype);
			pstmt.setString(2, start);
			pstmt.setString(3, end);
		}else if(!type_user_id.equals("")&&!type_log_type.equals("")&&!type_date_start.equals("")&&!type_date_end.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" LOG_TYPE=? ")
			.append(" AND USER_ID LIKE ? ")
			.append(" AND SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, logtype);
			pstmt.setString(2, userid);
			pstmt.setString(3, start);
			pstmt.setString(4, end);
		}else if(!type_user_id.equals("")&&!type_date_start.equals("")&&!type_date_end.equals("")&&type_log_type.equals("")&&type_date_ym.equals("")){
			qrybuf.append(" USER_ID LIKE ? ")
			.append(" AND SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, userid);
			pstmt.setString(2, start);
			pstmt.setString(3, end);
		}else if(!type_date_ym.equals("")&&type_user_id.equals("")&&type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
			qrybuf.append(" LOG_DATE LIKE ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, ym);
		}else if(!type_date_ym.equals("")&&!type_user_id.equals("")&&type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
			qrybuf.append(" LOG_DATE LIKE ? ")
					.append(" AND USER_ID LIKE ? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, ym);
			pstmt.setString(2, userid);
		}else if(!type_date_ym.equals("")&&type_user_id.equals("")&&!type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
			qrybuf.append(" LOG_DATE LIKE ? ")
					.append(" AND LOG_TYPE=? ");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, ym);
			pstmt.setString(2, logtype);
		}else if(!type_date_ym.equals("")&&!type_user_id.equals("")&&!type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
			qrybuf.append(" LOG_DATE LIKE ? ")
					.append(" AND LOG_TYPE=? ")
					.append(" AND USER_ID LIKE ?");
			pstmt = con.prepareStatement(qrybuf.toString());
			pstmt.setString(1, ym);
			pstmt.setString(2, logtype);
			pstmt.setString(3, userid);
		}
		debug.debug("cntLog ==> "+qrybuf.toString());	                                                                                                                                                                                                                         
		rs = pstmt.executeQuery();

		if ( rs.next() ){
			total_log = rs.getInt("TOT_USER_CNT");
		}

		rs.close();
		pstmt.close();

	}catch (SQLException e){
		throw e;
	}finally{
		if ( rs != null) try{rs.close();}catch(SQLException ee){}
		if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
	}

    return total_log;
}
/**
 * @author MYM
 * 검색별 검색 리스트를 가져온다
 * **/
public LogDataDO[] getTypeDateLogList(int pageNum, int list_cnt,  
		String type_date_start,String type_date_end, String type_log_type,String type_user_id,String type_date_ym) throws SQLException{
	PreparedStatement pstmt=null;
	ResultSet rs = null;
	Vector v_log = new Vector();
	LogDataDO log_data_info = null;
	LogDataDO[] log_datas = null;
	String start=type_date_start;
	String end=type_date_end;
	String logtype=type_log_type;
	String userid="%"+type_user_id+"%";
	String ym=type_date_ym+"%";

	try{
		int startCount = (pageNum-1)*list_cnt;
		StringBuffer qrybuf = new StringBuffer();
		qrybuf.append("SELECT")
				.append("  A.LOG_SERIAL LOG_SERIAL, A.USER_ID USER_ID, A.ORG_CODE ORG_CODE ")
				.append("  ,A.FILE_NAME FILE_NAME, A.FILE_SIZE FILE_SIZE, A.USER_IP USER_IP ")
				.append("  ,A.LOG_TYPE LOG_TYPE, A.FDIR_NAME FDIR_NAME, A.LOG_DATE LOG_DATE ")
				.append(" FROM JX_DRIVE_LOG A WHERE ");
			if(!type_log_type.equals("")&&type_user_id.equals("")&&type_date_start.equals("")&&type_date_end.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" LOG_TYPE=? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, logtype);
				pstmt.setInt(2, startCount);
				pstmt.setInt(3, list_cnt);
			}else if(!type_user_id.equals("")&&type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" USER_ID LIKE ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, userid);
				pstmt.setInt(2, startCount);
				pstmt.setInt(3, list_cnt);
			}else if(!type_date_start.equals("")&&!type_date_end.equals("")&&type_user_id.equals("")&&type_log_type.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, start);
				pstmt.setString(2, end);
				pstmt.setInt(3, startCount);
				pstmt.setInt(4, list_cnt);
			}else if(!type_user_id.equals("")&&!type_user_id.equals("")&&type_date_start.equals("")&&type_date_end.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" LOG_TYPE=? ")
				.append(" AND USER_ID LIKE ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, logtype);
				pstmt.setString(2, userid);
				pstmt.setInt(3, startCount);
				pstmt.setInt(4, list_cnt);
			}else if(!type_log_type.equals("")&&!type_date_start.equals("")&&!type_date_end.equals("")&&type_user_id.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" LOG_TYPE=? ")
				.append(" AND SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, logtype);
				pstmt.setString(2, start);
				pstmt.setString(3, end);
				pstmt.setInt(4, startCount);
				pstmt.setInt(5, list_cnt);
			}else if(!type_user_id.equals("")&&!type_log_type.equals("")&&!type_date_start.equals("")&&!type_date_end.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" LOG_TYPE=? ")
				.append(" AND USER_ID LIKE ? ")
				.append(" AND SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, logtype);
				pstmt.setString(2, userid);
				pstmt.setString(3, start);
				pstmt.setString(4, end);
				pstmt.setInt(5, startCount);
				pstmt.setInt(6, list_cnt);
			}else if(!type_user_id.equals("")&&!type_date_start.equals("")&&!type_date_end.equals("")&&type_log_type.equals("")&&type_date_ym.equals("")){
				qrybuf.append(" USER_ID LIKE ? ")
				.append(" AND SUBSTR(LOG_DATE,1,8) BETWEEN ? AND ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, userid);
				pstmt.setString(2, start);
				pstmt.setString(3, end);
				pstmt.setInt(4, startCount);
				pstmt.setInt(5, list_cnt);
			}else if(!type_date_ym.equals("")&&type_user_id.equals("")&&type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
				qrybuf.append(" LOG_DATE LIKE ? ")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, ym);
				pstmt.setInt(2, startCount);
				pstmt.setInt(3, list_cnt);
			}else if(!type_date_ym.equals("")&&!type_user_id.equals("")&&type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
				qrybuf.append(" LOG_DATE LIKE ? ")
				.append(" AND USER_ID LIKE ?")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, ym);
				pstmt.setString(2, userid);
				pstmt.setInt(3, startCount);
				pstmt.setInt(4, list_cnt);
			}else if(!type_date_ym.equals("")&&type_user_id.equals("")&&!type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
				qrybuf.append(" LOG_DATE LIKE ? ")
				.append(" AND LOG_TYPE=?")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, ym);
				pstmt.setString(2, logtype);
				pstmt.setInt(3, startCount);
				pstmt.setInt(4, list_cnt);
			}else if(!type_date_ym.equals("")&&!type_user_id.equals("")&&!type_log_type.equals("")&&type_date_start.equals("")&&type_date_end.equals("")){
				qrybuf.append(" LOG_DATE LIKE ? ")
				.append(" AND LOG_TYPE=?")
				.append(" AND USER_ID LIKE ?")
				.append(" ORDER BY LOG_DATE DESC LIMIT ?,?");
				pstmt = con.prepareStatement(qrybuf.toString());
				pstmt.setString(1, ym);
				pstmt.setString(2, logtype);
				pstmt.setString(3, userid);
				pstmt.setInt(4, startCount);
				pstmt.setInt(5, list_cnt);
			}

		
		debug.debug("<!> getLogList Query ==> "+qrybuf.toString());

	rs=pstmt.executeQuery();
	while(rs.next()){
		log_data_info=new LogDataDO();
		log_data_info.log_serial = rs.getInt("LOG_SERIAL");
		log_data_info.domain = serverName;
		log_data_info.user_id = rs.getString("USER_ID");
		log_data_info.org_code = rs.getString("ORG_CODE");
		log_data_info.file_name = rs.getString("FILE_NAME");
		log_data_info.file_size = rs.getDouble("FILE_SIZE");
		log_data_info.user_ip = rs.getString("USER_IP");
		log_data_info.log_type = rs.getString("LOG_TYPE");
		log_data_info.fdir_name = rs.getString("FDIR_NAME");
		log_data_info.log_date = rs.getString("LOG_DATE");
		
		v_log.add(log_data_info);
		log_data_info = null;
	}
	rs.close();
	pstmt.close();


	int log_cnt = v_log.size();
	if(log_cnt>0){
		log_datas = new LogDataDO[log_cnt];
		for(int i=0;i<log_cnt;i++){
			log_datas[i] = (LogDataDO)v_log.elementAt(i);

		}
	}
}catch (SQLException e){
	throw e;
}finally{
	if ( rs != null) try{rs.close();}catch(SQLException ee){}
	if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
}
return log_datas;
}

}

	

